Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary

''' <summary>
''' [To be supplied.]
''' </summary>
''' <remarks>[To be supplied.]</remarks>
<Serializable()> _
<SqlUserDefinedType(Format.UserDefined, MaxByteSize:=8000)> _
Public Class QueryBuilder

    Implements INullable
    Implements IBinarySerialize

    Private internalIsNull As Boolean
    Private internalQuery As String
    Private internalIsStoredProcedure As Boolean
    Private internalParameters As New List(Of Parameter)

    Private internalSqlMetaDataConstructor As SqlMetaDataConstructor = SqlMetaDataConstructor.None
    Private internalSqlMetaDataDbType As System.Data.SqlDbType
    Private internalSqlMetaDataMaxLength As Integer
    Private internalSqlMetaDataPrecision As Byte
    Private internalSqlMetaDataScale As Byte

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="storedProcedureName">[To be supplied.]</param>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public Shared Function New_StoredProcedure(ByVal storedProcedureName As SqlString) As QueryBuilder

        Dim myQueryBuilder As New QueryBuilder()
        myQueryBuilder.SetQueryAsStoredProcedure(storedProcedureName)

        Return myQueryBuilder

    End Function

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="queryText">[To be supplied.]</param>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public Shared Function New_QueryText(ByVal queryText As SqlString) As QueryBuilder

        Dim myQueryBuilder As New QueryBuilder()
        myQueryBuilder.SetQueryAsText(queryText)

        Return myQueryBuilder

    End Function

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public Overrides Function ToString() As String

        Dim myStringBuilder As New StringBuilder

        myStringBuilder.AppendFormat("Query: {0}{1}", Me.internalQuery, vbCrLf)
        myStringBuilder.AppendFormat("{2}Is Stored Procedure: {0}{1}", Me.internalIsStoredProcedure, vbCrLf, vbTab)
        myStringBuilder.AppendFormat("{1}Parameters number: {0}", Me.internalParameters.Count, vbTab)

        If Me.internalParameters.Count <> 0 Then

            For Each myParameter As Parameter In Me.internalParameters

                myStringBuilder.AppendFormat("{1}{2}{2}Parameter name: {0}", myParameter.Name, vbCrLf, vbTab)

                If myParameter.Value.Equals(DBNull.Value) Then

                    myStringBuilder.AppendFormat("{1}{2}{2}Parameter value: DBNull.Value", vbCrLf, vbTab)

                Else

                    myStringBuilder.AppendFormat("{1}{2}{2}Parameter value: {0}", myParameter.Value.ToString(), vbCrLf, vbTab)

                End If


            Next

        End If

        Return myStringBuilder.ToString()

    End Function

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull

        Get

            Return internalIsNull

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public Shared ReadOnly Property Null() As QueryBuilder

        Get

            Dim myQueryBuilder As QueryBuilder = New QueryBuilder
            myQueryBuilder.internalIsNull = True

            Return myQueryBuilder

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="value">[To be supplied.]</param>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Public Shared Function Parse(ByVal value As SqlString) As QueryBuilder

        Throw New NotSupportedException()

    End Function

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="dbType">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    <SqlMethod(DataAccess:=DataAccessKind.Read, IsDeterministic:=False, IsMutator:=True)> _
    Public Sub SetContentType(ByVal dbType As SqlString)

        Me.internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType
        Me.internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), dbType.Value), System.Data.SqlDbType)

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="dbType">[To be supplied.]</param>
    ''' <param name="maxLength">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    <SqlMethod(DataAccess:=DataAccessKind.Read, IsDeterministic:=False, IsMutator:=True)> _
    Public Sub SetContentType2(ByVal dbType As SqlString, ByVal maxLength As Integer)

        Me.internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType2
        Me.internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), dbType.Value), System.Data.SqlDbType)
        Me.internalSqlMetaDataMaxLength = maxLength

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="dbType">[To be supplied.]</param>
    ''' <param name="precision">[To be supplied.]</param>
    ''' <param name="scale">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    <SqlMethod(DataAccess:=DataAccessKind.Read, IsDeterministic:=False, IsMutator:=True)> _
    Public Sub SetContentType3(ByVal dbType As SqlString, ByVal precision As Byte, ByVal scale As Byte)

        Me.internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType3
        Me.internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), dbType.Value), System.Data.SqlDbType)
        Me.internalSqlMetaDataPrecision = precision
        Me.internalSqlMetaDataScale = scale

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="name">[To be supplied.]</param>
    ''' <param name="value">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    <SqlMethod(DataAccess:=DataAccessKind.Read, IsDeterministic:=False, IsMutator:=True)> _
    Public Sub SetParameter(ByVal name As SqlString, ByVal value As Object)

        If name.IsNull Then

            Throw New ArgumentException("parameterName can't be null")

        End If

        Me.internalIsNull = False

        If value Is Nothing Then

            internalParameters.Add(New Parameter(name.Value, DBNull.Value))

        Else

            internalParameters.Add(New Parameter(name.Value, value))

        End If


    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property Query() As String

        Get

            Return internalQuery

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property IsStoredProcedure() As Boolean

        Get

            Return internalIsStoredProcedure

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property Parameters() As List(Of Parameter)

        Get

            Return Me.internalParameters

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property SqlMetaDataConstructor() As SqlMetaDataConstructor

        Get

            Return internalSqlMetaDataConstructor

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property SqlMetaDataDbType() As System.Data.SqlDbType

        Get

            Return internalSqlMetaDataDbType

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property SqlMetaDataMaxLength() As Integer

        Get

            If Me.internalSqlMetaDataConstructor <> SqlMetaDataConstructor.SetContentType2 Then

                Throw New ArgumentException("This value is not available in this context!")

            End If

            Return internalSqlMetaDataMaxLength

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property SqlMetaDataPrecision() As Byte

        Get

            If Me.internalSqlMetaDataConstructor <> SqlMetaDataConstructor.SetContentType3 Then

                Throw New ArgumentException("This value is not available in this context!")

            End If

            Return internalSqlMetaDataPrecision

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <value>[To be supplied.]</value>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Friend ReadOnly Property SqlMetaDataScale() As Byte

        Get

            If Me.internalSqlMetaDataConstructor <> SqlMetaDataConstructor.SetContentType3 Then

                Throw New ArgumentException("This value is not available in this context!")

            End If

            Return internalSqlMetaDataScale

        End Get

    End Property

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="w">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    Friend Sub Write(ByVal w As System.IO.BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write

        If Me.internalIsNull Then

            w.Write(True)
            Return

        End If

        w.Write(False)
        w.Write(internalQuery)
        w.Write(internalIsStoredProcedure)

        Select Case internalSqlMetaDataConstructor

            Case SqlMetaDataConstructor.None
                w.Write(0)

            Case SqlMetaDataConstructor.SetContentType
                w.Write(1)
                w.Write(internalSqlMetaDataDbType.ToString())

            Case SqlMetaDataConstructor.SetContentType2
                w.Write(2)
                w.Write(internalSqlMetaDataDbType.ToString())
                w.Write(internalSqlMetaDataMaxLength)

            Case SqlMetaDataConstructor.SetContentType3
                w.Write(3)
                w.Write(internalSqlMetaDataDbType.ToString())
                w.Write(internalSqlMetaDataPrecision)
                w.Write(internalSqlMetaDataScale)

        End Select

        w.Write(internalParameters.Count)

        For Each parameter As Parameter In internalParameters

            w.Write(parameter.Name)

            If parameter.Value Is Nothing Then

                w.Write(True)

            Else

                w.Write(False)

                Dim myArray() As Byte = GetBytesArrayFromObject(parameter.Value)
                w.Write(myArray.Length)
                w.Write(myArray)

            End If

        Next

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="r">[To be supplied.]</param>
    ''' <remarks></remarks>
    Friend Sub Read(ByVal r As System.IO.BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read

        Me.internalIsNull = r.ReadBoolean()
        If Me.internalIsNull Then

            Return

        End If

        Me.internalQuery = r.ReadString()
        Me.internalIsStoredProcedure = r.ReadBoolean()

        Dim currentSqlMetaDataConstructor As Integer = r.ReadInt32()

        Select Case currentSqlMetaDataConstructor

            Case 0
                internalSqlMetaDataConstructor = SqlMetaDataConstructor.None

            Case 1
                internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType
                internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), r.ReadString()), System.Data.SqlDbType)

            Case 2
                internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType2
                internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), r.ReadString()), System.Data.SqlDbType)
                internalSqlMetaDataMaxLength = r.ReadInt32()

            Case 3
                internalSqlMetaDataConstructor = SqlMetaDataConstructor.SetContentType3
                internalSqlMetaDataDbType = DirectCast(System.Enum.Parse(GetType(System.Data.SqlDbType), r.ReadString()), System.Data.SqlDbType)
                internalSqlMetaDataPrecision = r.ReadByte()
                internalSqlMetaDataScale = r.ReadByte()

        End Select

        Dim parametersCount As Integer = r.ReadInt32()

        For index As Integer = 0 To parametersCount - 1

            Dim parameterName As String = r.ReadString()
            If r.ReadBoolean() Then

                Me.internalParameters.Add(New Parameter(parameterName, DBNull.Value))

            Else

                Me.internalParameters.Add(New Parameter(parameterName, GetObjectFromBytesArray(r.ReadBytes(r.ReadInt32()))))

            End If

        Next

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="storedProcedureName">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    Private Sub SetQueryAsStoredProcedure(ByVal storedProcedureName As SqlString)

        If storedProcedureName.IsNull Then

            Throw New ArgumentException("storedProcedureName can't be null")

        End If

        Me.internalIsNull = False
        Me.internalIsStoredProcedure = True
        Me.internalQuery = storedProcedureName.Value

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="queryText">[To be supplied.]</param>
    ''' <remarks>[To be supplied.]</remarks>
    Private Sub SetQueryAsText(ByVal queryText As SqlString)

        If queryText.IsNull Then

            Throw New ArgumentException("queryText can't be null")

        End If

        Me.internalIsNull = False
        Me.internalIsStoredProcedure = False
        Me.internalQuery = queryText.Value

    End Sub

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="myObject">[To be supplied.]</param>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Private Function GetBytesArrayFromObject(ByVal myObject As Object) As Byte()

        Dim myMemoryStream As New MemoryStream
        Dim myBinaryFormatter As New BinaryFormatter()
        myBinaryFormatter.Serialize(myMemoryStream, myObject)
        myMemoryStream.Close()

        Return myMemoryStream.ToArray()

    End Function

    ''' <summary>
    ''' [To be supplied.]
    ''' </summary>
    ''' <param name="myArray">[To be supplied.]</param>
    ''' <returns>[To be supplied.]</returns>
    ''' <remarks>[To be supplied.]</remarks>
    Private Function GetObjectFromBytesArray(ByVal myArray As Byte()) As Object

        Dim myMemoryStream As New MemoryStream(myArray)
        Dim myBinaryFormatter As New BinaryFormatter()
        Dim myObject As Object = myBinaryFormatter.Deserialize(myMemoryStream)
        myMemoryStream.Close()

        Return myObject

    End Function

End Class

